#!/usr/bin/perl -w
use strict;

use FindBin;
use lib ("$FindBin::Bin/..", "/net/cpp-group/Leo/bin");
use db_parameters;
use ensembl_parameters;

use DBI;


print STDERR <<"HEADLINE";
888888888888888888888888888888888888888888888888888888888888888888888

    table_taxon.gene_go_matches

	Description:
		Obtain GO terms and descriptions
	Populates DB tables:
		taxon.gene_go_matches
		GO_term_descriptions

888888888888888888888888888888888888888888888888888888888888888888888

HEADLINE

#_________________________________________________________________________________________
#
#
#	retrieve_GO_ids_per_category
#
#_________________________________________________________________________________________
sub retrieve_GO_ids_per_category($$$\%\%)
{
	my ($dbh, $table_name, $field, $gene_id_to_GO_ids, $GO_id_to_description) = @_;

	eval
	{
		my $sql_data = $dbh->selectall_arrayref(<<"PL/SQLCMD");
		SELECT 
			gene_stable_id AS gene_id, 
			$field AS GO_id, 
			description
		FROM 
			$table_name
		WHERE
			description IS NOT null;
PL/SQLCMD

		for my $line(@$sql_data)
		{
			$line->[1] =~ s/GO:(.*)/$1/;
			++$gene_id_to_GO_ids->{$line->[0]}{$line->[1]};
			$GO_id_to_description->{$line->[1]} = $line->[2];
		}
	};
	if ($@)
	{
		die $@ unless $@ =~ /Table.*exist/;
		print STDERR $@, "\n";
		return 0;
	}
	return 1;
	
}

#_________________________________________________________________________________________
#
#
#	retrieve_GO_ids
#
#_________________________________________________________________________________________
sub retrieve_GO_ids($$$\%\%)
{
	my ($dbh, $species, $ens_species, $gene_id_to_GO_ids, $GO_id_to_description) = @_;

	my @category_data = 
		($ens_mart_version < 28) ?
		(
		 ["BP" , "glook_biol_process_id", "$ens_species\_gene_ensembl__go_biol_process__look" ],
		 ["CL", "glook_cell_location_id", "$ens_species\_gene_ensembl__go_cell_location__look"],
		 ["MF", "glook_mol_function_id", "$ens_species\_gene_ensembl__go_mol_function__look"  ])
		:
		(
		 ["BP" , "glook_biol_process_id", "$ens_species\_go_biol_process__go_biol_process__main" ],
		 ["CL", "glook_cell_location_id", "$ens_species\_go_cell_location__go_cell_location__main"],
		 ["MF", "glook_mol_function_id", "$ens_species\_go_mol_function__go_mol_function__main"  ]);
	my $table_exists = 0;
	for (@category_data)
	{
		my ($GO_cat, $field,  $table) = @$_;
		$table_exists += retrieve_GO_ids_per_category($dbh, $table, $field, 
													 %{$gene_id_to_GO_ids->{$species}{$GO_cat}},
													 %{$GO_id_to_description->{$GO_cat}});
	}
	if ($table_exists)
	{
		return 1;
	}
	return 0;
}

	
#
#	Get DB parameters
#
my @db_param = get_ensembl_taxon_data();
my $dbh = connect_to_panda();


#
#	connect to ensembl db
#
my $ens_db_name = get_ensmart_db_name();
my $ens_dbh = DBI->connect("dbi:mysql:$ens_db_name:$ens_db_host", "$ens_db_user", '',
						{
							RaiseError => 1,
							AutoCommit => 1,
							PrintError => 0
						}
					)
or die "Database connection not made: DBI::errstr()";



my (%gene_id_to_GO_ids, %GO_id_to_description);
for my $taxon_data(@db_param)
{
	print STDERR "\tGet GO data for $taxon_data->[TAXON]\n";
	next if ($taxon_data->[ENS_TAXON] eq 'custom');
	if (!retrieve_GO_ids($ens_dbh, $taxon_data->[TAXON], $taxon_data->[ENS_TAXON_ABBREV], 
					%gene_id_to_GO_ids, %GO_id_to_description))
	{
		print STDERR "\tNo GO data for $taxon_data->[TAXON]\n";
	}
}


#
#	Enter GO terms per gene_id
#
print STDERR "\tEnter GO terms into panda\n";
$dbh->do("TRUNCATE taxon.gene_go_matches;");
$dbh->do("VACUUM ANALYSE taxon.gene_go_matches;");
$dbh->do("COPY taxon.gene_go_matches(gene_id, GO_id, species) FROM STDIN");
for my $species(keys %gene_id_to_GO_ids)
{
	my $go_per_species = $gene_id_to_GO_ids{$species};
	for my $GO_cat(keys %$go_per_species)
	{
		my $go_per_go_cat = $go_per_species->{$GO_cat};
		my $copy_data = '';
		for my $gene_id (keys %$go_per_go_cat)
		{
			my $go_per_gene_id = $go_per_go_cat->{$gene_id};
			for my $GO_id(keys %$go_per_gene_id)
			{
				next if $GO_id =~ /[^0-9]/;
				$copy_data .="$gene_id\t$GO_id\t$species\n";
			}
		}
		$dbh->pg_putline($copy_data);
	}
}
$dbh->pg_putline("\\.\n");
$dbh->pg_endcopy;
$dbh->do("ANALYSE taxon.gene_go_matches;");


#
#	Enter GO terms descriptions
#
print STDERR "\tEnter GO descriptions into panda\n";
$dbh->do("TRUNCATE public.GO_term_descriptions;");
$dbh->do("COPY public.GO_term_descriptions(GO_id, GO_category, description) FROM STDIN");
for my $GO_cat(keys %GO_id_to_description)
{
	for my $GO_id(keys %{$GO_id_to_description{$GO_cat}})
	{
		next if $GO_id =~ /[^0-9]/;
		$dbh->pg_putline("$GO_id\t$GO_cat\t$GO_id_to_description{$GO_cat}{$GO_id}\n");
	}
}
$dbh->pg_putline("\\.\n");
$dbh->pg_endcopy;
$dbh->do("ANALYSE public.GO_term_descriptions;");


print STDERR "\tCompleted\n";

$dbh->disconnect();
